﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace KaoQin.BLL
{
    public class SpecialCmd
    {
        public SpecialCmd() { }

        private readonly KaoQin.DAL.SpecialCmd dal = new DAL.SpecialCmd();

        public object GetSingle(string sql)
        {
            return dal.GetSingle(sql);
        }

        public DataSet Query(string sql)
        {
            return dal.Query(sql);
        }

        public string GetNormal(DateTime BeginTime, DateTime EndTime)
        {
            int empTotal = new Employee().GetRecordCount("KState=1");
            StringBuilder sql=new StringBuilder();
            sql.Append("select count(1) from Employee a ");
            sql.Append("where exists (");
            sql.Append("select 1 from SwipeCardRecord b where a.ID=b.EmployeeID");
            sql.AppendFormat(" and b.SwipeCardTime>=datetime('{0} 00:00:00') and b.SwipeCardTime<=datetime('{1} 23:59:59') and b.KState=0)",
                BeginTime.ToString("yyyy-MM-dd"),
                EndTime.ToString("yyyy-MM-dd"));
            sql.Append(" and a.KState=1");

            int normal = int.Parse(GetSingle(sql.ToString()).ToString());
            return string.Format("{0}/{1}", normal, empTotal);
        }

        public string GetLate(DateTime BeginTime, DateTime EndTime)
        {
            
            StringBuilder sql = new StringBuilder();
            sql.Append("select count(1) from Employee a ");
            sql.Append("where exists (");
            sql.Append("select 1 from SwipeCardRecord b where a.ID=b.EmployeeID");
            sql.AppendFormat(" and b.SwipeCardTime>=datetime('{0} 00:00:00') and b.SwipeCardTime<=datetime('{1} 23:59:59') and b.KState=1)",
                BeginTime.ToString("yyyy-MM-dd"),
                EndTime.ToString("yyyy-MM-dd"));
            sql.Append(" and a.KState=1");
            sql.Append(" and not exists(");
            sql.Append(" select 1 from SwipeCardRecord c where a.ID=c.EmployeeID");
            sql.AppendFormat("  and c.SwipeCardTime>=datetime('{0} 00:00:00') and c.SwipeCardTime<=datetime('{1} 23:59:59') and c.KState=0)",
                 BeginTime.ToString("yyyy-MM-dd"),
                EndTime.ToString("yyyy-MM-dd"));
            int late = int.Parse(GetSingle(sql.ToString()).ToString());
            return late.ToString();
        }

        public string GetFeiCount(DateTime BeginTime, DateTime EndTime)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select count(1) from SwipeCardRecord");
            sql.AppendFormat(" where SwipeCardTime>=datetime('{0} 00:00:00') and SwipeCardTime<=datetime('{1} 23:59:59') and KState=4",
                BeginTime.ToString("yyyy-MM-dd"),
                EndTime.ToString("yyyy-MM-dd"));

            return GetSingle(sql.ToString()).ToString();
            
        }

       

        public string GetNo(DateTime BeginTime, DateTime EndTime)
        {

            StringBuilder sql = new StringBuilder();
            sql.Append("select count(1) from Employee a ");
            sql.Append("where not exists (");
            sql.Append("select 1 from SwipeCardRecord b where a.ID=b.EmployeeID");
            sql.AppendFormat(" and b.SwipeCardTime>=datetime('{0} 00:00:00') and b.SwipeCardTime<=datetime('{1} 23:59:59') and (b.KState=0 or b.KState=1))",
                BeginTime.ToString("yyyy-MM-dd"),
                EndTime.ToString("yyyy-MM-dd"));
            sql.Append(" and a.KState=1");
            int no = int.Parse(GetSingle(sql.ToString()).ToString());
            return no.ToString();
        }
        public string GetEndNo(DateTime BeginTime, DateTime EndTime)
        {

            StringBuilder sql = new StringBuilder();
            sql.Append("select count(1) from Employee a ");
            sql.Append("where not exists (");
            sql.Append("select 1 from SwipeCardRecord b where a.ID=b.EmployeeID");
            sql.AppendFormat(" and b.SwipeCardTime>=datetime('{0} 00:00:00') and b.SwipeCardTime<=datetime('{1} 23:59:59') and (b.KState=2 or b.KState=3))",
                BeginTime.ToString("yyyy-MM-dd"),
                EndTime.ToString("yyyy-MM-dd"));
            sql.Append(" and a.KState=1");
            int no = int.Parse(GetSingle(sql.ToString()).ToString());
            return no.ToString();
        }

        public string GetEndEarly(DateTime BeginTime, DateTime EndTime)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select count(1) from Employee a ");
            sql.Append("where  exists (");
            sql.Append("select 1 from SwipeCardRecord b where a.ID=b.EmployeeID");
            sql.AppendFormat(" and b.SwipeCardTime>=datetime('{0} 00:00:00') and b.SwipeCardTime<=datetime('{1} 23:59:59') and b.KState=3)",
                BeginTime.ToString("yyyy-MM-dd"),
                EndTime.ToString("yyyy-MM-dd"));
            sql.Append(" and a.KState=1");
            int Early = int.Parse(GetSingle(sql.ToString()).ToString());
            return Early.ToString();
        }

        public string GetEndPercent(DateTime BeginTime, DateTime EndTime)
        {
            int empTotal = new Employee().GetRecordCount("KState=1");
            StringBuilder sql = new StringBuilder();
            sql.Append("select count(1) from Employee a ");
            sql.Append("where exists (");
            sql.Append("select 1 from SwipeCardRecord b where a.ID=b.EmployeeID");
            sql.AppendFormat(" and b.SwipeCardTime>=datetime('{0} 00:00:00') and b.SwipeCardTime<=datetime('{1} 23:59:59') and b.KState=2)",
                BeginTime.ToString("yyyy-MM-dd"),
                EndTime.ToString("yyyy-MM-dd"));
            sql.Append(" and a.KState=1");

            int Percent = int.Parse(GetSingle(sql.ToString()).ToString());
            return ((float.Parse(Percent.ToString()) / float.Parse(empTotal.ToString())) * 100).ToString("0.00") + "%";
        }

        public string GetAfterNormal(DateTime BeginTime, DateTime EndTime)
        {
            int empTotal = new Employee().GetRecordCount("KState=1");
            StringBuilder sql = new StringBuilder();
            sql.Append("select count(1) from Employee a ");
            sql.Append("where exists (");
            sql.Append("select 1 from SwipeCardRecord b where a.ID=b.EmployeeID");
            sql.AppendFormat(" and b.SwipeCardTime>=datetime('{0} 00:00:00') and b.SwipeCardTime<=datetime('{1} 23:59:59') and b.KState=2)",
                BeginTime.ToString("yyyy-MM-dd"),
                EndTime.ToString("yyyy-MM-dd"));
            sql.Append(" and a.KState=1");

            return GetSingle(sql.ToString()).ToString() + "/" + empTotal;
        }

        public DataSet GetBeginByPage(int state, DateTime beginTime, DateTime endTime, string KName, string worknum,int pageSize,int pageIndex)
        {
            
            int offsetCount = (pageIndex - 1) * pageSize;
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ID,  EmployeeID, MAX(SwipeCardTime) AS SwipeCardTime, MAX(PhotoUrl) AS PhotoUrl, KState ,");
            sql.Append("(select KName  from employee where ID=EmployeeID) as KName ,(select WordNum from employee where ID=EmployeeID) as WordNum ");
            sql.Append(" FROM SwipeCardRecord");
            sql.AppendFormat(" where SwipeCardTime>=datetime('{0} 00:00:00') and SwipeCardTime<=datetime('{1} 23:59:59') and KState={2}",
                beginTime.ToString("yyyy-MM-dd"),
                endTime.ToString("yyyy-MM-dd"),
                state);
            if (state==1)
            {
                sql.Append(" and not exists(");
                sql.AppendFormat("select 1 from SwipeCardRecord b where b.ID=SwipeCardRecord.ID  and b.SwipeCardTime>=datetime('{0} 00:00:00') and b.SwipeCardTime<=datetime('{1} 23:59:59') and b.KState={2})",
                    beginTime.ToString("yyyy-MM-dd"),
                endTime.ToString("yyyy-MM-dd"),
                0);
            }
            if (!string.IsNullOrEmpty(KName))
            {
                sql.AppendFormat(" and  EmployeeID in (select ID from employee where KName like '%{0}%') ", KName);
            }
            if (!string.IsNullOrEmpty(worknum))
            {
                sql.AppendFormat(" and  EmployeeID in (select ID from employee where WordNum like '%{0}%') ", worknum);
            }
            sql.Append(" GROUP BY EmployeeID, KState  order by EmployeeID");
            sql.AppendFormat(" limit {0} offset {1}",pageSize,offsetCount);

            return Query(sql.ToString());
        }

        public DataSet GetEndByPage(int state, DateTime beginTime, DateTime endTime, string KName, string worknum, int pageSize, int pageIndex)
        {

            int offsetCount = (pageIndex - 1) * pageSize;
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ID,  EmployeeID, MIN(SwipeCardTime) AS SwipeCardTime, MIN(PhotoUrl) AS PhotoUrl, KState ,");
            sql.Append("(select KName  from employee where ID=EmployeeID) as KName ,(select WordNum from employee where ID=EmployeeID) as WordNum ");
            sql.Append(" FROM SwipeCardRecord");
            sql.AppendFormat(" where SwipeCardTime>=datetime('{0} 00:00:00') and SwipeCardTime<=datetime('{1} 23:59:59') and KState={2}",
                beginTime.ToString("yyyy-MM-dd"),
                endTime.ToString("yyyy-MM-dd"),
                state);
            if (!string.IsNullOrEmpty(KName))
            {
                sql.AppendFormat(" and  EmployeeID in (select ID from employee where KName like '%{0}%') ", KName);
            }
            if (!string.IsNullOrEmpty(worknum))
            {
                sql.AppendFormat(" and  EmployeeID in (select ID from employee where WordNum like '%{0}%') ", worknum);
            }
            sql.Append(" GROUP BY EmployeeID, KState  order by EmployeeID");
            sql.AppendFormat(" limit {0} offset {1}", pageSize, offsetCount);

            return Query(sql.ToString());
        }

        public DataSet ExportPData(string strWhere)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT (select WordNum from employee where ID=EmployeeID) as 员工工号,");
            sql.Append(" PhotoUrl as 照片,");
            sql.Append("(select KName  from employee where ID=EmployeeID) as 员工姓名,");
            sql.Append("SwipeCardTime as 打卡时间,");
            sql.Append("(case  KState  when 0 then '正常上班' when 1 then '上班迟到' when 2 then '正常下班' when 3 then '下班早退' when 4 then '非上班时间' when 5 then '加班时间' else '未知' end)as 状态 ");
            sql.Append(" from SwipeCardRecord ");
            sql.Append(strWhere);
            sql.Append(" order by 员工工号,SwipeCardTime");

            return Query(sql.ToString());

        }

        public DataSet GetFeiList(DateTime beginTime, DateTime endTime, string KName, string worknum, int pageSize, int pageIndex)
        {
            int offsetCount = (pageIndex - 1) * pageSize;
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT   EmployeeID, SwipeCardTime, PhotoUrl , KState ,");
            sql.Append("(select KName  from employee where ID=EmployeeID) as KName ,(select WordNum from employee where ID=EmployeeID) as WordNum ");
            sql.Append(" FROM SwipeCardRecord");
            sql.AppendFormat(" where SwipeCardTime>=datetime('{0} 00:00:00') and SwipeCardTime<=datetime('{1} 23:59:59') and KState={2}",
                beginTime.ToString("yyyy-MM-dd"),
                endTime.ToString("yyyy-MM-dd"),
                4);
            if (!string.IsNullOrEmpty(KName))
            {
                sql.AppendFormat(" and  EmployeeID in (select ID from employee where KName like '%{0}%') ", KName);
            }
            if (!string.IsNullOrEmpty(worknum))
            {
                sql.AppendFormat(" and  EmployeeID in (select ID from employee where WordNum like '%{0}%') ", worknum);
            }
            sql.Append(" order by EmployeeID");
            sql.AppendFormat(" limit {0} offset {1}", pageSize, offsetCount);

            return Query(sql.ToString());
        }

        


        public DataSet GetBeginNoList(DateTime BeginTime, DateTime EndTime, string KName, string worknum, int pageSize, int pageIndex)
        {
            int offsetCount = (pageIndex - 1) * pageSize;
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from Employee a ");
            sql.Append("where not exists (");
            sql.Append("select 1 from SwipeCardRecord b where a.ID=b.EmployeeID");
            sql.AppendFormat(" and b.SwipeCardTime>=datetime('{0} 00:00:00') and b.SwipeCardTime<=datetime('{1} 23:59:59') and (b.KState=0 or b.KState=1))",
                BeginTime.ToString("yyyy-MM-dd"),
                EndTime.ToString("yyyy-MM-dd"));
            sql.Append(" and a.KState=1");
            if (!string.IsNullOrEmpty(KName))
            {
                sql.AppendFormat(" and   a.KName like '%{0}%' ", KName);
            }
            if (!string.IsNullOrEmpty(worknum))
            {
                sql.AppendFormat(" and a.WordNum like '%{0}%' ", worknum);
            }
            sql.Append(" order by a.ID");
            sql.AppendFormat(" limit {0} offset {1}", pageSize, offsetCount);

            return Query(sql.ToString());
        }

        public DataSet GetEndNoList(DateTime BeginTime, DateTime EndTime, string KName, string worknum, int pageSize, int pageIndex)
        {
            int offsetCount = (pageIndex - 1) * pageSize;
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from Employee a ");
            sql.Append("where not exists (");
            sql.Append("select 1 from SwipeCardRecord b where a.ID=b.EmployeeID");
            sql.AppendFormat(" and b.SwipeCardTime>=datetime('{0} 00:00:00') and b.SwipeCardTime<=datetime('{1} 23:59:59') and (b.KState=2 or b.KState=3))",
                BeginTime.ToString("yyyy-MM-dd"),
                EndTime.ToString("yyyy-MM-dd"));
            sql.Append(" and a.KState=1");
            if (!string.IsNullOrEmpty(KName))
            {
                sql.AppendFormat(" and   a.KName like '%{0}%' ", KName);
            }
            if (!string.IsNullOrEmpty(worknum))
            {
                sql.AppendFormat(" and a.WordNum like '%{0}%' ", worknum);
            }
            sql.Append(" order by a.ID");
            sql.AppendFormat(" limit {0} offset {1}", pageSize, offsetCount);

            return Query(sql.ToString());
        }



        public DataSet GetDetialList(string state, DateTime beginTime, DateTime endTime, string KName, int pageSize, int pageIndex,out int recordCount)
        {
            int offsetCount = (pageIndex - 1) * pageSize;
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ID,   EmployeeID, SwipeCardTime, PhotoUrl , KState ,");
            sql.Append("(select KName  from employee where ID=EmployeeID) as KName ,(select WordNum from employee where ID=EmployeeID) as WordNum ");
            sql.Append(" FROM SwipeCardRecord");
            

            StringBuilder strWhere = new StringBuilder();

            strWhere.AppendFormat(" where SwipeCardTime>=datetime('{0} 00:00:00') and SwipeCardTime<datetime('{1} 00:00:00') ",
                beginTime.ToString("yyyy-MM-dd"),
                endTime.ToString("yyyy-MM-dd"));

            if (state!= "-1")
            {
                strWhere.Append(" and KState=" + state + "");
            }

            if (!string.IsNullOrEmpty(KName))
            {
                strWhere.AppendFormat(" and  EmployeeID in (select ID from employee where KName like '%{0}%') ", KName);
            }
            sql.Append(strWhere);
            sql.Append(" order by EmployeeID");
            sql.AppendFormat(" limit {0} offset {1}", pageSize, offsetCount);
            recordCount = int.Parse(GetSingle("select count(1) from SwipeCardRecord " + strWhere).ToString());

            return Query(sql.ToString());
        }

        public DataSet GetdayDetialList(DateTime beginTime, DateTime endTime, string KName/*, int pageSize, int pageIndex,out int recordCount*/)
        {
            //int offsetCount = (pageIndex - 1) * pageSize;
            StringBuilder sql = new StringBuilder();
            sql.Append("select b.[WordNum] as '员工编号',b.[KName] '员工姓名',b.[PhotoUrl] as '员工照片',");
            sql.Append("date(a.days) as '考勤日期',");
            sql.Append("max(case when duan=11 then strftime('%H:%M',SwipeCardTime) else null end) as '上班1',");
            sql.Append("max(case when duan=11 then c.PhotoUrl else null end) as '上班1照片',");
            sql.Append("max(case when duan=12 then strftime('%H:%M',SwipeCardTime) else null end) as '下班1',");
            sql.Append("max(case when duan=12 then c.PhotoUrl else null end) as '下班1照片',");
            sql.Append("max(case when duan=21 then strftime('%H:%M',SwipeCardTime) else null end) as '上班2',");
            sql.Append("max(case when duan=21 then c.PhotoUrl else null end) as '上班2照片',");
            sql.Append("max(case when duan=22 then strftime('%H:%M',SwipeCardTime) else null end) as '下班2',");
            sql.Append("max(case when duan=22 then c.PhotoUrl else null end) as '下班2照片',");
            sql.Append("max(case when duan=31 then strftime('%H:%M',SwipeCardTime) else null end) as '上班3',");
            sql.Append("max(case when duan=31 then c.PhotoUrl else null end) as '上班3照片',");
            sql.Append("max(case when duan=32 then strftime('%H:%M',SwipeCardTime) else null end) as '下班3',");
            sql.Append("max(case when duan=32 then c.PhotoUrl else null end) as '下班3照片'");
            sql.Append(" from daylist a ");
            sql.Append("  inner join Employee b on 1=1");
            sql.Append(" left join SwipeCardRecord c on b. ID=c.employeeid and date(a.days)=date(c.CreateTime)");
            sql.Append("   and not exists(select 1 from SwipeCardRecord x where c.employeeid=x.employeeid and date(c.SwipeCardTime)=date(x.SwipeCardTime) and c.SwipeCardTime>x.SwipeCardTime and c.duan=x.duan)");
            sql.AppendFormat(" where a.days>=datetime('{0} 00:00:00') and a.days<datetime('{1} 00:00:00') ",
                beginTime.ToString("yyyy-MM-dd"),
                endTime.ToString("yyyy-MM-dd"));
            string strWhere = string.Empty;
            if (!string.IsNullOrEmpty(KName))
            {
                sql.AppendFormat(" and   b.KName like '%{0}%' ", KName);
            }

            sql.Append(" group by b.id,a.days");
            //sql.AppendFormat(" limit {0} offset {1}", pageSize, offsetCount);

            //StringBuilder strCount = new StringBuilder();
            //strCount.Append("select Count(1) from  daylist a ");
            //strCount.Append("  inner join Employee b on 1=1");
            //strCount.AppendFormat(" where a.days>=datetime('{0} 00:00:00') and a.days<datetime('{1} 00:00:00') ",
            //    beginTime.ToString("yyyy-MM-dd"),
            //    endTime.ToString("yyyy-MM-dd"));
            //strCount.Append(" and b.KState=1 ");
            //if (!string.IsNullOrEmpty(KName))
            //{
            //    strCount.AppendFormat(" and   b.KName like '%{0}%') ", KName);
            //}

            //recordCount = int.Parse(GetSingle(strCount.ToString()).ToString());
            return Query(sql.ToString());
        }

        public DataSet ExportAll(string state, DateTime beginTime, DateTime endTime, string KName)
        {
          
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT ");
            sql.Append(" (select WordNum from employee where ID=EmployeeID) as 员工工号,");
            sql.Append(" PhotoUrl as 照片,");
            sql.Append("(select KName  from employee where ID=EmployeeID) as 员工姓名 , ");
            sql.Append("SwipeCardTime as 打卡时间,");
            sql.Append("(case  KState  when 0 then '正常上班' when 1 then '上班迟到' when 2 then '正常下班' when 3 then '下班早退' when 4 then '非上班时间' when 5 then '加班时间' else '未知' end)as 状态 ");
            sql.Append(" FROM SwipeCardRecord");
            sql.AppendFormat(" where SwipeCardTime>=datetime('{0} 00:00:00') and SwipeCardTime<datetime('{1} 00:00:00') ",
                beginTime.ToString("yyyy-MM-dd"),
                endTime.ToString("yyyy-MM-dd"));

            if (state != "-1")
            {
                sql.Append(" and KState=" + state + "");
            }

            if (!string.IsNullOrEmpty(KName))
            {
                sql.AppendFormat(" and  EmployeeID in (select ID from employee where KName like '%{0}%') ", KName);
            }

            sql.Append(" order by 员工工号,SwipeCardTime");
            

            return Query(sql.ToString());
        }


    }
}
